<?php

class Application_Model_MemberMapper
{
	protected $_dbTable;
	
	public function setDbTable($dbTable)
	{
		if (is_string($dbTable)) {
			$dbTable = new $dbTable();
		}
		if (!$dbTable instanceof Zend_Db_Table_Abstract) {
			throw new Exception('Invalid table data gateway provided');
		}
		$this->_dbTable = $dbTable;
		return $this;
	}
	
	public function getDbTable()
	{
		if (null === $this->_dbTable) {
			$this->setDbTable('Application_Model_DbTable_Member');
		}
		return $this->_dbTable;
	}
	
	public function insertAll(Application_Model_Member $obj)
    {
        $data = array(
            'username'   => $obj->getUsername(),
            'password' => $obj->getPassword(),
        	'email'   => $obj->getEmail(),
        	'phone'   => $obj->getPhone(),
            'member_type' => $obj->getMember_type(),
        	'job'   => $obj->getJob(),
            'gender' => $obj->getGender(),
            'travel' => $obj->getTravel(),
        	'birth' => $obj->getBirth(),
        	'residence' => $obj->getResidence(),
        	'image' => $obj->getImage(),
        	'description' => $obj->getDescription(),
        	'deal_credit' => $obj->getDeal_credit(),
        	'rating_credit' => $obj->getRating_credit(),
        	'total_deal' => $obj->getTotal_deal(),
        	'creation_time' => $obj->getCreation_time(),
        	'code' => $obj->getCode()
        );
        $this->getDbTable()->insert($data);
    }
    
	public function updateAll(Application_Model_Member $obj)
    {
    	$db = $this->getDbTable();
    	
        $data = array(
        	'username'   => $obj->getUsername(),
            'password' => $obj->getPassword(),
        	'email'   => $obj->getEmail(),
        	'phone'   => $obj->getPhone(),
            'member_type' => $obj->getMember_type(),
        	'job'   => $obj->getJob(),
            'gender' => $obj->getGender(),
        	'travel' => $obj->getTravel(),
        	'birth' => $obj->getBirth(),
        	'residence' => $obj->getResidence(),
        	'image' => $obj->getImage(),
        	'description' => $obj->getDescription(),
        	'deal_credit' => $obj->getDeal_credit(),
        	'rating_credit' => $obj->getRating_credit(),
        	'total_deal' => $obj->getTotal_deal(),
        	'creation_time' => $obj->getCreation_time(),
        	'code' => $obj->getCode()
        );
        $db->update($data,array('member_id = ?' => $obj->getMember_id()));
    }
    
	public function findByResult($select){
		$db = $this->getDbTable();
    	$resultSet = $db->fetchAll($select);
        $entries   = array();
		foreach ($resultSet as $row) {
			$entry = new Application_Model_Member();
			$entry->setMember_id($row->member_id)
				->setUsername($row->username)
				->setPassword($row->password)
				->setEmail($row->email)
				->setPhone($row->phone)
				->setMember_type($row->member_type)
				->setJob($row->job)
				->setBirth($row->birth)
				->setResidence($row->residence)
				->setGender($row->gender)
				->setTravel($row->travel)
				->setImage($row->image)
				->setDescription($row->description)
				->setDeal_credit($row->deal_credit)
				->setRating_credit($row->rating_credit)
				->setTotal_deal($row->total_deal)
				->setCreation_time($row->creation_time)
				->setCode($row->code);
			$entries[] = $entry;
		}
		return $entries;
    }
        
	public function findByUsername($username)
    {
    	if($username!=null){
	    	$db = $this->getDbTable();
	        $select = $db->select()->from('member')
	             ->where('username = ?', $username);
	    	
	    	return $this->findByResult($select);
    	}
    }
    
	public function findByEmail($email)
    {
    	if($email!=null){
	    	$db = $this->getDbTable();
	        $select = $db->select()->from('member')
	             ->where('email = ?', $email);
	    	
	    	return $this->findByResult($select);
    	}
    }
    
	public function findByMemberId($member_id)
    {
    	if($member_id!=null){
	    	$db = $this->getDbTable();
	        $select = $db->select()->from('member')
	             ->where('member_id = ?', $member_id);
	    	
	    	return $this->findByResult($select);
    	}
    }
    
	public function deleteValidatedMember($phone)
    {
        $this->getDbTable()->delete(array(
        		'phone = ?' => $phone,
        		'code != ?' => 'Y'
        ));
    }
    
	public function findByProductBrowse()
    {
	    $db = $this->getDbTable();
	    	
    	$select = $db->select()
	    	->from(array('c'=>'product_browse'), array('amount' => 'SUM(amount)'))
	    	->join(array('b'=>'product_buy'),'b.product_buy_id = c.product_buy_id', array('product_buy_id' => 'MAX(b.product_buy_id)', 'title'))
			->join(array('a'=>'member'), 'b.member_id = a.member_id')
			->join(array('d'=>'country'),'a.travel = d.abbrev', array('country_name'))
			->group('member_id')
			->order('product_buy_id DESC');
		
		$select->setIntegrityCheck(false); 

    	$resultSet = $db->fetchAll($select);
        $entries   = array();
		foreach ($resultSet as $row) {
			$entry = new Application_Model_Member();
			$entry->setMember_id($row->member_id)
				->setUsername($row->username)
				->setPassword($row->password)
				->setEmail($row->email)
				->setPhone($row->phone)
				->setMember_type($row->member_type)
				->setJob($row->job)
				->setBirth($row->birth)
				->setResidence($row->residence)
				->setGender($row->gender)
				->setTravel($row->country_name)
				->setImage($row->image)
				->setDescription($row->description)
				->setDeal_credit($row->deal_credit)
				->setRating_credit($row->rating_credit)
				->setTotal_deal($row->total_deal)
				->setCreation_time($row->creation_time)
				->setCode($row->code)
				->setAmount($row->amount)
				->setProduct_buy_id($row->product_buy_id)
				->setTitle($row->title);
			$entries[] = $entry;
		}
		return $entries;
    }
    
	public function findScheduleByUsername($username)
    {
   		if($username!=null){
	    	$db = $this->getDbTable();
	        $select = $db->select()->from('member')
	             ->where('username LIKE ?', '%' . $username . '%');
	    	
	    	return $this->findByResult($select);
    	}
    }
    
	public function findScheduleByUsernameCountry($username, $country, $year, $month, $day)
    {
    	$db = $this->getDbTable();
    	if($username!=null){
	    	$select = $db->select()
			->from(array('a'=>'member'), array('mid' => 'a.member_id', 'username', 'password', 'email', 'phone', 'member_type', 'job',
				'birth', 'residence', 'gender', 'travel', 'image', 'description', 'deal_credit', 'rating_credit', 'total_deal', 'creation_time', 'code'))
			->join(array('b'=>'calendar_events'),'a.member_id = b.member_id')
			->where('username LIKE ?', '%' . $username . '%')
			->where('event_desc = ?', $country)
			->where('event_year >= ?', $year)
			->where('event_month >= ?', $month)
			->where('event_day >= ?', $day)
			->group('a.member_id');
    	}
    	else{
    		$select = $db->select()
			->from(array('a'=>'member'), array('mid' => 'a.member_id', 'username', 'password', 'email', 'phone', 'member_type', 'job',
				'birth', 'residence', 'gender', 'travel', 'image', 'description', 'deal_credit', 'rating_credit', 'total_deal', 'creation_time', 'code'))
			->join(array('b'=>'calendar_events'),'a.member_id = b.member_id')
			->where('event_desc = ?', $country)
			->where('event_year >= ?', $year)
			->where('event_month >= ?', $month)
			->where('event_day >= ?', $day)
			->group('a.member_id');
    	}
    	
    	$select->setIntegrityCheck(false); 
			
		$resultSet = $db->fetchAll($select);
        $entries   = array();
		foreach ($resultSet as $row) {
			$entry = new Application_Model_Member();
			$entry->setMember_id($row->mid)
				->setUsername($row->username)
				->setPassword($row->password)
				->setEmail($row->email)
				->setPhone($row->phone)
				->setMember_type($row->member_type)
				->setJob($row->job)
				->setBirth($row->birth)
				->setResidence($row->residence)
				->setGender($row->gender)
				->setTravel($row->travel)
				->setImage($row->image)
				->setDescription($row->description)
				->setDeal_credit($row->deal_credit)
				->setRating_credit($row->rating_credit)
				->setTotal_deal($row->total_deal)
				->setCreation_time($row->creation_time)
				->setCode($row->code);
			$entries[] = $entry;
		}
		return $entries;
    }
    
    public function findByRandom(){
    	$db = $this->getDbTable();
        $select = $db->select()->from('member')
             ->order(new Zend_Db_Expr('RAND()'));
    	
    	return $this->findByResult($select);
	}
}

